/**
 * @FileName: KnowledgeBaseService.java
 * @Author
 * @Description:
 * @Date 2017年3月8日 上午8:34:44
 * @CopyRight ZTE Corporation
 */
package cn.hpclub.server.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.AbstractMap.SimpleEntry;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONObject;
import com.jfinal.core.JFinal;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;

import cn.hpclub.server.bean.OrgNode;
import cn.hpclub.server.bean.SystemConfig;
import cn.hpclub.server.controller.admin.BaseAdminController;
import cn.hpclub.server.model.KnowledgeBase;
import cn.hpclub.server.util.ExcelLargeDataUtils;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.SystemConfigUtil;
import cn.hpclub.server.util.excel.ExcelLogs;
import cn.hpclub.server.util.excel.ExcelUtil;

public class KnowledgeBaseService{
    private static final Logger              logger  = LoggerFactory.getLogger(KnowledgeBaseService.class);
    public static final KnowledgeBaseService service = new KnowledgeBaseService();

    /**
     * @Title: exportData
     * @Description:导出数据
     * @param select查询SELECT
     * @param sqlExceptSelect查询条件
     * @return JSONObject导出结果
     */
    public JSONObject exportData(String select, String sqlExceptSelect){
        String[] headers = new String[] { "名称", "类型", "资费编码", "可见地市", "是否政企", "发布者 ", "浏览量 ", "更新时间", "详情描述" };
        String[] columns = new String[] { "name", "type", "code", "city_zone", "is_gov", "created", "pv_count",
                "modi_date", "detail" };
        String name = generateOutputFileName();
        String filePath = getFilePath(name);
        JSONObject jo = null;
        try{
            String exportFileUrl = ExcelLargeDataUtils.exportData(headers, columns, select + sqlExceptSelect, name,
                                                                  filePath);
            jo = createDownloadSuccessJSONObject(exportFileUrl);
        }
        catch(IOException e){
            e.printStackTrace();
            jo = createDownloadErrorJSONObject(e);
        }
        return jo;
    }

    private JSONObject createDownloadErrorJSONObject(IOException e){
        JSONObject jo = new JSONObject();
        jo.put("status", 0);
        jo.put("message", "导出失败：" + e.getMessage());
        return jo;
    }

    private JSONObject createDownloadSuccessJSONObject(String exportFileUrl){
        JSONObject jo = new JSONObject();
        jo.put("status", 1);
        jo.put("message", "导出成功，点击下载");
        jo.put("url", exportFileUrl);
        return jo;
    }

    private String generateOutputFileName(){
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HHmmssSSS");
        String curTime = sdf.format(date);
        String fileName = "知识库导出数据" + curTime + ".xlsx";
        return fileName;
    }

    /**
     * @Title: importData
     * @Description:知识库导入
     * @param controller
     * @param file导入文件
     * @return Map<String,Object>结果
     */
    public Map<String, Object> importData(BaseAdminController controller, UploadFile file){
        String resultMsg = null;
        int status = 1;
        try{
            Collection<Map> importExcel = getImportCollection(file);

            Result result = saveImportData2Db(controller, importExcel);
            resultMsg = getResultMsg(result);
        }
        catch(Exception e){
            status = 0;
            resultMsg = handleImportException(e);
        }
        Map<String, Object> jsonMap = controller.buildUploadReturnMsg(status, resultMsg);
        return jsonMap;
    }

    /**
     * @Title: handleUploadException
     * @Description:导入异常处理
     * @param e
     * @return String
     */
    private String handleImportException(Exception e){
        String resultMsg1 = null;
        if(e.getClass().equals(FileNotFoundException.class)){
            resultMsg1 = "文件不存在";
        } else if(e.getClass().equals(OfficeXmlFileException.class)
                || e.getClass().equals(EncryptedDocumentException.class)
                || e.getClass().equals(InvalidFormatException.class)){
            e.printStackTrace();
            resultMsg1 = "格式错误，请上传excel 2003/2007格式文件";
        } else{
            resultMsg1 = "服务器处理故障";
        }
        return resultMsg1;
    }

    /**
     * @Description:生成导入结果
     */
    private String getResultMsg(Result result){
        String resultMsg;
        String url;
        resultMsg = result.getResultMsg();
        if(result.getErrorCount() < 10){
            for(String r : result.getResultList()){
                resultMsg += "\r\n" + r;
            }
        } else{
            url = saveResult2TmpFile(result);
            resultMsg += "\r\n导入详情见文件：\r\n" + url;
        }
        return resultMsg;
    }

    /**
     * @Description:保存导入结果到临时文件
     */
    private String saveResult2TmpFile(Result result){
        String fileName = "知识库上传结果" + System.currentTimeMillis() + ".txt";
        String filePath = getFilePath(fileName);
        writeReusltToFile(result, filePath);
        String fileUrl = SystemConfigUtil.getSystemConfig().getHostPrefix() + SystemConfig.UPLOAD_FILE_DIR + fileName;
        return fileUrl;
    }

    /**
     * @Description:结果写入文件
     */
    private void writeReusltToFile(Result result, String filePath){
        File file = new File(filePath);
        FileWriter writer = null;
        try{
            writer = new FileWriter(file);
            writer.write(result.getResultMsg());
            writer.write("\r\n");;
            for(String error : result.getResultList()){
                writer.write(error);
                writer.write("\r\n");
            }
        }
        catch(IOException e){
            e.printStackTrace();
        }
        finally{
            if(writer != null){
                try{
                    writer.close();
                }
                catch(IOException e){
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * @Description:获取文件路径
     */
    private String getFilePath(String fileName){
        String filePath = JFinal.me().getServletContext().getRealPath(SystemConfig.UPLOAD_FILE_DIR + fileName);
        String fileDir = SystemConfigUtil.getSystemConfig().getUploadDir();
        logger.debug("cmccbusiness fileDir = " + fileDir);
        if(!StringUtils.isEmpty(fileDir)){
            String dirPath = fileDir + "/file/";
            File dir = new File(dirPath);
            if(!dir.exists()){
                dir.mkdirs();
            }
            filePath = dirPath + fileName;
        }
        return filePath;
    }

    /**
     * @Title: getImportCollection
     * @Description:从上传Excel文件中获取数据集
     * @param file上传文件
     */
    private Collection<Map> getImportCollection(UploadFile file) throws FileNotFoundException, InvalidFormatException{
        InputStream inputStream = null;
        inputStream = new FileInputStream(file.getFile());

        ExcelLogs logs = new ExcelLogs();
        Collection<Map> importExcel = ExcelUtil.importExcel(Map.class, inputStream, "yyyy/MM/dd HH:mm:ss", logs, 0);
        return importExcel;
    }

    /**
     * @Description:保存上传数据到数据库
     */
    private Result saveImportData2Db(BaseAdminController controller, Collection<Map> importExcel){
        Result result = new Result();
        int successCount = 0;
        int errorCount = 0;
        int index = 1;
        List<String> resultList = new ArrayList<String>();
        int govFlag = InterfaceUtil.getGovEnterpriseFlag();
        int adminId = InterfaceUtil.getAdminId();
        int createdOrgId = InterfaceUtil.getOrgId();
        for(Map<?, ?> m : importExcel){
            try{
                validImportExcelCellData(m);
                String typeId = getTypeId(m);
                int[] cityIds = getCityIds(m);
                saveData2Db(controller, typeId, cityIds, getString(String.valueOf(m.get("名称")), 50), getCode(m),
                            getDetail(m), govFlag, getEnabled(m), adminId, createdOrgId);
                successCount++;
            }
            catch(IOException e){
                logger.error(e.getMessage());
                resultList.add(String.format("第%d条：%s", index, e.getMessage()));
                errorCount++;
            }
            index++;
        }
        result.setResultMsg(String.format("成功：%d\r\n失败：%d", successCount, errorCount));
        result.setResultList(resultList);
        result.setErrorCount(errorCount);
        return result;
    }

    /**
     * @Title: saveData2Db
     * @Description:保存单条记录到数据库
     * @param adminId
     *            TODO
     * @param createdOrgId
     *            TODO
     * @param govFlag政企表示
     * @param enabled是否启用
     * @param typeId类型ID
     * @param cityIds可见城市ID集合
     * @param name名称
     * @param code资费编码
     * @param details详情
     *            void
     */
    private void saveData2Db(BaseAdminController controller, String typeId, int[] cityIds, String name, String code,
            String details, int govFlag, int enabled, int adminId, int createdOrgId){
        KnowledgeBase model = KnowledgeBase.dao.getModelByName(name, govFlag, createdOrgId);
        if(model == null){
            addRecord(controller, name, code, typeId, cityIds, details, govFlag, enabled, adminId, createdOrgId);
        } else{
            updateRecord(model, name, code, details, typeId, cityIds, govFlag, enabled, adminId, createdOrgId);
        }
    }

    /**
     * @Title: updateRecord
     * @Description:更新已存在知识库记录
     * @param enabled是否启用
     * @param govFlag是否是政企表示
     * @param name名称
     * @param code资费编码
     * @param details详情
     * @param typeId类型ID
     * @param cityIds可见城市集合
     *            void
     */
    private void updateRecord(KnowledgeBase model, String name, String code, String details, String typeId,
            int[] cityIds, int govFlag, int enabled, int adminId, int createdOrgId){
        model.set("name", name);
        model.set("code", code);
        model.set("type", typeId);
        model.set("detail", details);
        model.set("modi_date", new Date());
        model.set("created_by", adminId);
        model.set("created_org_id", createdOrgId);
        model.set("is_gov", govFlag);
        model.set("enabled", enabled);
        // 删除该知识库记录的原地市映射关系
        int knowledgeId = model.getLong("id").intValue();
        deleteKnowledageBaseAndCityMap(knowledgeId);
        addKnowledageBaseAndCityMap(knowledgeId, cityIds);
        model.update();
    }

    /**
     * @Title: deleteKnowledageBaseAndCityMap
     * @Description:删除该知识库记录的原地市映射关
     * @param knowledgeId
     *            void
     */
    private void deleteKnowledageBaseAndCityMap(int knowledgeId){
        Db.update("DELETE FROM knowledge_base_org WHERE knowledge_id=?", knowledgeId);
    }

    /**
     * @Title: addKnowledageBaseAndCityMap
     * @Description:新增知识库记录与地市关系记录
     * @param knowledgeId知识库ID
     * @param cityIds可见城市ID集合
     *            void
     */
    private void addKnowledageBaseAndCityMap(int knowledgeId, int[] cityIds){
        String sqlInsert = "INSERT INTO knowledge_base_org (knowledge_id, org_id) VALUES ";
        for(int index = 0; index < cityIds.length; index++){
            sqlInsert += String.format("(%d,%d)", knowledgeId, cityIds[index]);
            if(index < (cityIds.length - 1)){
                sqlInsert += ",";
            }
        }
        logger.info("sqlInsert:" + sqlInsert);
        int count = Db.update(sqlInsert);
        logger.info("update Count:" + count);
    }

    /**
     * @Title: addRecord
     * @Description:新增知识库记录
     * @param admin
     *            TODO
     * @param createdOrgId
     *            TODO
     * @param enabled是否启用
     * @param govFlag是否是政企标识
     * @param name名称
     * @param code资费编码
     * @param details详情
     * @param typeId类型ID
     * @param cityIds可见城市ID集合
     *            void
     */
    private void addRecord(BaseAdminController controller, String name, String code, String typeId, int[] cityIds,
            String details, int govFlag, int enabled, int adminId, int createdOrgId){
        KnowledgeBase model;
        model = controller.getModel(KnowledgeBase.class);
        model.set("name", name);
        model.set("code", code);
        model.set("type", typeId);
        model.set("detail", details);
        model.set("modi_date", new Date());
        model.set("created_date", new Date());
        model.set("created_by", adminId);
        model.set("created_org_id", createdOrgId);
        model.set("enabled", enabled);
        model.set("is_gov", govFlag);
        if(model.save()){
            int knowledgeId = model.getLong("id").intValue();
            addKnowledageBaseAndCityMap(knowledgeId, cityIds);
        }
    }

    private String getCode(Map<?, ?> m){
        return getString(ExcelUtil.removeSpace(String.valueOf(m.get("资费代码"))), 64);
    }

    private int getEnabled(Map<?, ?> m){
        return "是".equals(ExcelUtil.removeSpace(String.valueOf(m.get("是否有效")))) ? 1 : 0;
    }

    private String getDetail(Map<?, ?> m){
        return String.valueOf(m.get("详情描述"));
    }

    private int[] getCityIds(Map<?, ?> data) throws IOException{
        int provinceId = InterfaceUtil.getIdTop();
        int userOrgId = InterfaceUtil.getOrgId();
        if(userOrgId == provinceId){
            /* 省级账号可以配置全省 */
            String citys = String.valueOf(data.get("可见地市"));
            String[] cityArray = citys.split(",");
            Map<String, Integer> cityZoneMap = KnowledgeBaseService.service.getCityZoneMap();
            int[] cityIds = new int[cityArray.length];
            for(int i = 0; i < cityArray.length; i++){
                String city = cityArray[i];
                if(cityZoneMap.containsKey(city)){
                    cityIds[i] = cityZoneMap.get(city);
                } else{
                    throw new IOException("可见地市错误，不存在" + city);
                }
            }
            return cityIds;
        } else{
            /* 地市级账号只能配置地市 */
            return new int[] { userOrgId };
        }
    }

    private String getTypeId(Map<?, ?> data) throws IOException{
        String type = getString(ExcelUtil.removeSpace(String.valueOf(data.get("类型"))), 50);
        /* 检查类型是否合法 */
        Map<String, String> typeMap = KnowledgeBaseService.service.getTypeMap();
        if(!typeMap.containsKey(type)){
            throw new IOException("不存在知识库类型：" + type);
        }
        String typeId = typeMap.get(type);
        return typeId;
    }

    private void validImportExcelCellData(Map<?, ?> data) throws IOException{
        String[] names = null;
        if(isProvinceAccount()){
            names = new String[] { "名称", "类型", "可见地市", "详情描述", "是否有效" };
        } else{
            names = new String[] { "名称", "类型", "详情描述", "是否有效" };
        }
        for(int i = 0; i < names.length; i++){
            String value = String.valueOf(data.get(names[i]));
            if(StringUtils.isBlank(value) || "null".equalsIgnoreCase(value)){
                logger.error("validImportExcelCellData data error:" + data.get(names[i] + ":" + value));
                throw new IOException(names[i] + "不能为空");
            }
        }
    }

    private boolean isProvinceAccount(){
        int provinceId = InterfaceUtil.getIdTop();
        int userOrgId = InterfaceUtil.getOrgId();
        boolean isProvince = userOrgId == provinceId;
        return isProvince;
    }

    public List<Entry<String, String>> getTypeList(){
        List<Record> list = getTypeListFromDb();
        List<Entry<String, String>> typeList = new ArrayList<Entry<String, String>>();
        typeList.add(new SimpleEntry<String, String>("全部", "0"));
        for(Record r : list){
            typeList.add(new SimpleEntry<String, String>(r.getStr("name"), r.getStr("value")));
        }
        return typeList;
    }

    public List<Entry<String, Integer>> getTypeListForDetail(){
        List<Record> list = getTypeListFromDb();
        List<Entry<String, Integer>> typeList = new ArrayList<Entry<String, Integer>>();
        for(Record r : list){
            typeList.add(new SimpleEntry<String, Integer>(r.getStr("name"), Integer.parseInt(r.getStr("value"))));
        }
        return typeList;
    }

    public List<Record> getkbOrgByKbId(int kbId){
        String sql = "SELECT * FROM knowledge_base_org WHERE knowledge_id=?";
        List<Record> list = Db.find(sql, kbId);
        return list;
    }

    private List<Record> getTypeListFromDb(){
        List<Record> list = Db
                .find("SELECT name, value,sort  FROM sys_code_value WHERE type_id = (SELECT id FROM sys_code_type WHERE code='KNOWLEDGE_BASE_TYPE') ORDER BY sort ASC");
        return list;
    }

    /**
     * @Title: getOrgMaps
     * @Description:获取市级分公司构集合
     * @return Map<String,Integer>
     */
    public List<Entry<String, String>> getCityLevelOrgList(){
        List<Entry<String, String>> list = new ArrayList<Entry<String, String>>();

        OrgNode root = InterfaceUtil.getOrgTree(InterfaceUtil.getIdTop(), 1, false);
        if(root == null || root.getSubNode() == null || root.getSubNode().size() == 0){
            return list;
        }
        /* 大掌柜根节点，显示所有地市分公司 */
        int accountOrgId = InterfaceUtil.getOrgId();
        if(accountOrgId == root.getId()){
            list.add(new SimpleEntry<String, String>("全省", String.valueOf(root.getId())));
            for(OrgNode node : root.getSubNode()){
                list.add(new SimpleEntry<String, String>(node.getDisplayName(), String.valueOf(node.getId())));
            }
        } else{
            for(OrgNode node : root.getSubNode()){
                if(accountOrgId == node.getId()){
                    list.add(new SimpleEntry<String, String>(node.getDisplayName(), String.valueOf(node.getId())));
                }
            }
        }
        return list;
    }

    public Map<String, String> getTypeMap(){
        Map<String, String> map = new HashMap<String, String>();
        List<Record> list = getTypeListFromDb();
        for(Record r : list){
            map.put(r.getStr("name"), r.getStr("value"));
        }
        return map;
    }

    public Map<String, Integer> getCityZoneMap(){
        Map<String, Integer> map = new HashMap<String, Integer>();
        OrgNode root = InterfaceUtil.getOrgTree(InterfaceUtil.getIdTop(), 1, false);
        if(root == null || root.getSubNode() == null || root.getSubNode().size() == 0){
            return map;
        }
        /* 大掌柜根节点，显示所有地市分公司 */
        int accountOrgId = InterfaceUtil.getOrgId();
        if(accountOrgId == root.getId()){
            for(OrgNode node : root.getSubNode()){
                map.put(node.getDisplayName(), node.getId());
            }
        } else{
            for(OrgNode node : root.getSubNode()){
                if(accountOrgId == node.getId()){
                    map.put(node.getDisplayName(), node.getId());
                }
            }
        }
        return map;
    }

    private String getString(String fullString, int maxLen){
        if(StringUtils.isBlank(fullString)){
            return "";
        }
        if("null".equalsIgnoreCase(fullString)){
            return "";
        }
        if(fullString.length() > maxLen){
            return fullString.substring(0, maxLen);
        }
        return fullString;
    }

    static class Result{
        private String       resultMsg;
        private List<String> resultList;
        private int          errorCount = 0;

        public String getResultMsg(){
            return resultMsg;
        }

        public void setResultMsg(String resultMsg){
            this.resultMsg = resultMsg;
        }

        public List<String> getResultList(){
            return resultList;
        }

        public void setResultList(List<String> resultList){
            this.resultList = resultList;
        }

        public void setErrorCount(int errorCount){
            this.errorCount = errorCount;
        }

        public int getErrorCount(){
            return errorCount;
        }
    }
}
